﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace WaveMaster.Holidays.Club.SQL
{
    public class TripSQL
    {
        public static string GetAllTrip()
        {
            string sql = "select * from trip";
            return sql;
        }

        public static string GetAllTripByDateAndSeaPort()
        {
            string sql = "select * from trip where m_seaPortID=?m_seaPortID and m_departureDate=?m_departureDate and m_avaliable='True'";
            return sql;
        }

        public static string GetAllTripByDateAndSeaPortBiggerDateTime()
        {
            string sql = "select * from trip where m_seaPortID=?m_seaPortID and m_departureDate=?m_departureDate and m_avaliable='True' and m_departureTime > ?m_departureTime";
            return sql;
        }

        public static string GetTripByID()
        {
            string sql = "select * from trip where m_tripID = ?m_tripID ";
            return sql;
        }
        public static string SearchTripByCodeName(string p)
        {
            string sql = "select * from trip t, vessel v, seaport p WHERE t.m_vesselID = v.m_vesselID and t.m_seaPortID = p.m_seaPortID and concat(m_tripCode, v.m_vesselName, p.m_seaPortName) like '%" + p + "%'";
            return sql;
        }
        public static string SearchTripByCodeNameAndDepartureDate(string p)
        {
            string sql = @"select * from trip t, vessel v, seaport p WHERE t.m_vesselID = v.m_vesselID 
            and t.m_seaPortID = p.m_seaPortID and concat(m_tripCode, v.m_vesselName, p.m_seaPortName) like '%" + p + @"%'
            and date(m_departureDate) = ?m_departureDate";
            return sql;
        }
        public static string DeleteTripByID()
        {
            return @"DELETE FROM trip
      WHERE m_tripID = ?m_tripID";
        }
        public static string InsertTrip()
        {
            string sql = @"INSERT INTO trip
           (m_tripCode
           ,m_vesselID
            ,m_seaPortID
            ,m_departureDate
            ,m_departureTime
           ,m_cretnDate            
            ,m_cretnBy
            ,m_modfctnDate
            ,m_modfctnBy
            ,m_avaliable
            ,m_destinationPortID
            ,m_departureStatus
            )
     VALUES
           (?m_tripCode
           ,?m_vesselID
            ,?m_seaPortID
            ,?m_departureDate
            ,?m_departureTime
           ,?m_cretnDate
            , ?m_cretnBy
            , ?m_modfctnDate
            ,?m_modfctnBy
            ,?m_avaliable
            ,?m_destinationPortID
            ,?m_departureStatus
            )";
            return sql;
        }
        public static string UpdateTrip()
        {
            string sql = @"UPDATE trip
   SET m_tripCode=?m_tripCode
           ,m_vesselID=?m_vesselID
            ,m_seaPortID=?m_seaPortID
            ,m_departureDate=?m_departureDate
            ,m_departureTime=?m_departureTime
            ,m_modfctnDate=?m_modfctnDate
            ,m_modfctnBy=?m_modfctnBy
            ,m_avaliable=?m_avaliable
            ,m_destinationPortID=?m_destinationPortID
            ,m_departureStatus=?m_departureStatus
 WHERE m_tripID = ?m_tripID";
            return sql;
        }

        public static string GetMaxTripID()
        {
            return @"SELECT MAX(m_tripID) FROM trip";
        }
    }
}
